﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;
using StudentsManager.Models;

namespace StudentsManager.DataAccess
{
    public class DatabaseHelper
    {
        public string dbPath = "StudentManagement.db";
        public string _connectionString = "Data Source=StudentManagement.db;Version=3;";

        public DatabaseHelper()
        {
            InitializeDatabase();
        }

        // 创建数据库文件和表
        public void InitializeDatabase()
        {
            // 如果数据库文件不存在，则创建它
            if (!File.Exists(dbPath))
            {
                SQLiteConnection.CreateFile(dbPath);
                CreateTables();
            }
        }

        public void ExecuteNonQuery(string query)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        // 创建表
        private void CreateTables()
        {
            using (var connection = new SQLiteConnection($"Data Source={dbPath};Version=3;"))
            {
                connection.Open();
                string createSchoolsTable = @"
                CREATE TABLE IF NOT EXISTS Schools (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Address TEXT NOT NULL
                );";

                string createClassesTable = @"
                CREATE TABLE IF NOT EXISTS Classes (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Description TEXT NOT NULL,
                    SchoolId INTEGER,
                    FOREIGN KEY (SchoolId) REFERENCES Schools(Id)
                );";

                string createStudentsTable = @"
                CREATE TABLE IF NOT EXISTS Students (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Age INTEGER NOT NULL,
                    Gender INTEGER NOT NULL,
                    ClassId INTEGER,
                    FOREIGN KEY (ClassId) REFERENCES Classes(Id)
                );";

                string createLogsTable = @"
                CREATE TABLE IF NOT EXISTS Logs (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Action TEXT NOT NULL,
                    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
                );";

                using (var command = new SQLiteCommand(createSchoolsTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createClassesTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createStudentsTable, connection))
                {
                    command.ExecuteNonQuery();
                }
                using (var command = new SQLiteCommand(createLogsTable, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}
